package com.dai.chun.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.dai.chun.dao.dao.JobDao;
import com.dai.chun.dao.model.Job;
import com.dai.chun.dbc.DBManager;
import com.dai.chun.util.PageSplitUtil;

public class JobDaoImpl implements JobDao {
private DBManager manager = DBManager.getInstance();
	

	public List<Job> listJob(PageSplitUtil psu) {
		String sql = "select * from t_job limit ?,?";
		List<Job> jobs = new ArrayList<Job>();
		Connection conn = null;
		ResultSet res=null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setInt(1, psu.getStart());
			preparedStatement.setInt(2,psu.getEnd());
			res = preparedStatement.executeQuery();
			
			while (res.next()) {
				Job job = new Job();
				job.setJob_id(res.getInt(1));
				job.setJob_name(res.getString(2));
				jobs.add(job);
			}
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, preparedStatement, res);
		}
		return jobs;
	}

	public Boolean save(Job job) throws SQLException {
		boolean flag = false;
		String sql = "insert into t_job (job_name,job_dept ,job_setup_time) values(?,?,?)";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setString( 1,job.getJob_name());
			//preparedStatement.setInt( 2,job.getJob_dept_id());
			preparedStatement.setString( 2,job.getJob_dept());
			preparedStatement.setString( 3,job.getJob_setup_time());
			int count = preparedStatement.executeUpdate(); // 
				if (count > 0) {
					flag = true;
				}
			} catch (Exception e) {
			} finally {
				manager.closeConn(conn, preparedStatement);
		}
		return flag;
	}

	@Override
	public int findAllCount() throws SQLException {
		int ret=0;
		String sql = "select count(*) from t_job";
		Connection conn = manager.getConnection();
		java.sql.Statement sm=conn.createStatement();
		ResultSet rs=sm.executeQuery(sql);
		if(rs.next()){
			ret=rs.getInt(1);
		}
		manager.closeConn(conn, sm,rs);
		return ret;
	}

	@Override
	public int delJob(int job_id) throws SQLException {
		int ret=0;
		String sql = "delete from t_job where job_id=?";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		preparedStatement.setInt(1 ,job_id);
		ret=preparedStatement.executeUpdate();
		manager.closeConn(conn, preparedStatement);
		return ret;
		
	}

	@Override
	public int updateJob(Job job) throws SQLException {
		int ret=0;
		String sql = "update t_job set job_name=? where job_id=?";
		Connection conn = manager.getConnection();
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setString( 1,job.getJob_name());
			preparedStatement.setInt(2,job.getJob_id());
			int count = preparedStatement.executeUpdate(); // 
				if (count > 0) {
					ret=1;
				}
			} catch (Exception e) {
			} finally {
				manager.closeConn(conn, preparedStatement);
		}
		
		return ret;
	}

	@Override
	public Job findJobById(int job_id) {
		String sql = "select * from t_job where job_id=?";
		Job job =new Job();
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		ResultSet res=null;
		try {
			conn = manager.getConnection();
			preparedStatement =  manager.createPRPstmt(conn, sql);
			preparedStatement.setInt(1 ,job_id);
			res = preparedStatement.executeQuery();
			while (res.next()) {
				job.setJob_id(res.getInt(1));
				job.setJob_name(res.getString(2));
				job.setJob_dept_id(res.getInt(3));
			}
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, preparedStatement,res);
		}
		return job;
	}

	@Override
	public List<Job> listJobByDeptId(PageSplitUtil psu, int dept_id) {
		String sql = "select * from t_job  where job_dept_id=? limit ?,?";
		List<Job> jobs = new ArrayList<Job>();
		Connection conn = null;
		ResultSet res=null;
		try {
			conn = manager.getConnection();
		} catch (SQLException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		PreparedStatement preparedStatement =  manager.createPRPstmt(conn, sql);
		try {
			preparedStatement.setInt(1,dept_id);
			preparedStatement.setInt(2, psu.getStart());
			preparedStatement.setInt(3,psu.getEnd());
			res = preparedStatement.executeQuery();
			
			while (res.next()) {
				Job job = new Job();
				job.setJob_id(res.getInt(1));
				job.setJob_name(res.getString(2));
				jobs.add(job);
			}
		} catch (Exception e) {
		} finally {
			manager.closeConn(conn, preparedStatement,res);
		}
		return jobs;
	}

}
